package com.ph.irp.base.assistantmaterial.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.mchange.v2.c3p0.ComboPooledDataSource;
import com.ph.irp.base.assistantmaterial.dao.PBaseAssistantOpenaccountbankDAO;
import com.ph.irp.model.PBaseAssistantmaterialOpenaccountbank;
import com.ph.util.DBUtil;

public class PBaseAssistantOpenaccountbankDAOImpl implements
		PBaseAssistantOpenaccountbankDAO {

	// 数据源
	private ComboPooledDataSource dataSource;

	// setter for dataSource
	public void setDataSource(ComboPooledDataSource dataSource) {

		this.dataSource = dataSource;

	}
	/**
	 * 新增出纳管理及开户行
	 */
	@Override
	public void addAccountbookBank(
			PBaseAssistantmaterialOpenaccountbank accountbookBankModel)
			throws Throwable {
		// TODO Auto-generated method stub
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//结果集
		ResultSet resultSet = null ;
		//新增sql语句
		StringBuilder sql = new StringBuilder();
		
		sql.append("insert into p_base_assistantmaterial_openaccountbank(OpenAccountBankCode,OpenAccountBankName) ") ;
		sql.append("values('") ;
		sql.append(accountbookBankModel.getOpenAccountBankCode()+"','") ;
		sql.append(accountbookBankModel.getOpenAccountBankName()+"')");
		System.out.println(sql.toString());
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql.toString()) ;
			
			statement.executeUpdate() ;
					
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
	}
	/**
	 * 根据id批量删除出纳管理及开户行
	 */
	@Override
	public void deleteAccountbookBankById(Object[] accountbookBankIds)
			throws Throwable {
		// TODO Auto-generated method stub
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "delete from p_base_assistantmaterial_openaccountbank where OpenAccountBankId=?";

		try {

			con = this.dataSource.getConnection();

			// statement = con.prepareStatement(sql) ;
			for (Object object : accountbookBankIds) {

				int accountbookBankId = Integer.valueOf((String) object);

				statement = con.prepareStatement(sql);

				statement.setInt(1, accountbookBankId);

				statement.execute();
			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
	}
	/**
	 * 查询所有的出纳管理及开户行
	 */
	@Override
	public List<PBaseAssistantmaterialOpenaccountbank> getAllAccountbookBanks()
			throws Throwable {
		// TODO Auto-generated method stub
		//出纳管理账号和开户行列表
		List<PBaseAssistantmaterialOpenaccountbank> bankList = new ArrayList<PBaseAssistantmaterialOpenaccountbank>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_openaccountbank ";

		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql);

			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (resultSet.next()) {
				//出纳管理账号和开户行
				PBaseAssistantmaterialOpenaccountbank accountbookBankModel = new PBaseAssistantmaterialOpenaccountbank();

				accountbookBankModel.setOpenAccountBankId(resultSet.getInt(1));

				accountbookBankModel
						.setOpenAccountBankCode(resultSet.getString(2));

				accountbookBankModel
						.setOpenAccountBankName(resultSet.getString(3));

				bankList.add(accountbookBankModel);

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return bankList;
	}
	/**
	 * 根据代码模糊查询出纳管理账号和开户行
	 */
	@Override
	public List<PBaseAssistantmaterialOpenaccountbank> getAccountbookBankByCode(
			String accountbookBankCode) throws Throwable {
		// TODO Auto-generated method stub
		//出纳管理账号和开户行列表
		List<PBaseAssistantmaterialOpenaccountbank> bankList = new ArrayList<PBaseAssistantmaterialOpenaccountbank>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_openaccountbank  where OpenAccountBankCode like ?";

		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql);
			statement.setString(1, "%"+accountbookBankCode+"%");
			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (resultSet.next()) {
				//出纳管理账号和开户行model
				PBaseAssistantmaterialOpenaccountbank accountbookBankModel = new PBaseAssistantmaterialOpenaccountbank();

				accountbookBankModel.setOpenAccountBankId(resultSet.getInt(1));

				accountbookBankModel
						.setOpenAccountBankCode(resultSet.getString(2));

				accountbookBankModel
						.setOpenAccountBankName(resultSet.getString(3));

				bankList.add(accountbookBankModel);

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (RuntimeException e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return bankList;
	}
	/**
	 * 根据id查询出纳管理及开户行
	 */
	@Override
	public PBaseAssistantmaterialOpenaccountbank getAccountbookBankById(
			Integer accountbookBankId) throws Throwable {
		// TODO Auto-generated method stub
		//出纳管理账号和开户行model
		PBaseAssistantmaterialOpenaccountbank accountbookBankModel = new PBaseAssistantmaterialOpenaccountbank();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_openaccountbank  where OpenAccountBankId = ?";

		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql);
			statement.setInt(1,accountbookBankId);
			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			if(resultSet.next()) {

				accountbookBankModel.setOpenAccountBankId(resultSet.getInt(1));

				accountbookBankModel
						.setOpenAccountBankCode(resultSet.getString(2));

				accountbookBankModel
						.setOpenAccountBankName(resultSet.getString(3));

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return accountbookBankModel;
	}
	/**
	 * 根据姓名模糊查询出纳管理及开户行
	 */
	@Override
	public List<PBaseAssistantmaterialOpenaccountbank> getAccountbookBankByName(
			String accountbookBankName) throws Throwable {
		// TODO Auto-generated method stub
		//出纳管理账号和开户行列表
		List<PBaseAssistantmaterialOpenaccountbank> bankList = new ArrayList<PBaseAssistantmaterialOpenaccountbank>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_openaccountbank  where OpenAccountBankName like ?";

		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql);
			statement.setString(1, "%"+accountbookBankName+"%");
			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (resultSet.next()) {
				//出纳管理账号和开户行model
				PBaseAssistantmaterialOpenaccountbank accountbookBankModel = new PBaseAssistantmaterialOpenaccountbank();

				accountbookBankModel.setOpenAccountBankId(resultSet.getInt(1));

				accountbookBankModel
						.setOpenAccountBankCode(resultSet.getString(2));

				accountbookBankModel
						.setOpenAccountBankName(resultSet.getString(3));

				bankList.add(accountbookBankModel);

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return bankList;
	}
	/**
	 * 根据name和code模糊查询出纳管理及开户行
	 */
	@Override
	public List<PBaseAssistantmaterialOpenaccountbank> getAccountbookBankByNameAndCode(
			String accountbookBankName, String accountbookBankCode)
			throws Throwable {
		// TODO Auto-generated method stub
		//出纳管理账号和开户行列表
		List<PBaseAssistantmaterialOpenaccountbank> bankList = new ArrayList<PBaseAssistantmaterialOpenaccountbank>();
		// 数据库连接
		Connection con = null;
		// 预备语句
		PreparedStatement statement = null;
		// 结果集
		ResultSet resultSet = null;
		// 查询语句
		String sql = "select * from p_base_assistantmaterial_openaccountbank  where OpenAccountBankName like ? and OpenAccountBankCode like ?";

		try {

			con = this.dataSource.getConnection();

			statement = con.prepareStatement(sql);
			statement.setString(1, "%"+accountbookBankName+"%");
			statement.setString(2, "%"+accountbookBankCode+"%");
			resultSet = statement.executeQuery();
			// 处理结果集中的记录到采购价格model
			while (resultSet.next()) {
				//出纳管理账号和开户行model
				PBaseAssistantmaterialOpenaccountbank accountbookBankModel = new PBaseAssistantmaterialOpenaccountbank();

				accountbookBankModel.setOpenAccountBankId(resultSet.getInt(1));

				accountbookBankModel
						.setOpenAccountBankCode(resultSet.getString(2));

				accountbookBankModel
						.setOpenAccountBankName(resultSet.getString(3));

				bankList.add(accountbookBankModel);

			}

		} catch (Exception e) {// 捕捉异常，并作相应的处理
			// TODO Auto-generated catch block

			throw new Exception(e.getMessage());

		} finally {// 释放资源，关闭连接

			try {
				DBUtil.setNullObject(con, statement, resultSet);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}
		}
		return bankList;
	}
	/**
	 * 更新出纳管理及开户行
	 */
	@Override
	public void updateAccountbookBank(
			PBaseAssistantmaterialOpenaccountbank accountbookBankModel)
			throws Throwable {
		// TODO Auto-generated method stub
		//数据库连接
		Connection con = null ;
		//预备语句
		PreparedStatement statement = null ;
		//修改sql语句
		StringBuilder sql = new StringBuilder();
		
		sql.append("update p_base_assistantmaterial_openaccountbank set OpenAccountBankCode='");
		sql.append(accountbookBankModel.getOpenAccountBankCode()+ "',");
		sql.append("OpenAccountBankName='"+accountbookBankModel.getOpenAccountBankName()) ;
		sql.append("' where OpenAccountBankId="+accountbookBankModel.getOpenAccountBankId()) ;
		
		try {
			
			con = this.dataSource.getConnection();
			
			statement = con.prepareStatement(sql.toString()) ;			
			statement.executeUpdate() ;		
			
		} catch (Exception e) {//捕捉异常，并作相应的处理
			// TODO Auto-generated catch block
			
			throw new Exception(e.getMessage());
			
		}finally{//释放资源，关闭连接
			
			try {
				DBUtil.setNullObject(con, statement, null);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				throw new Exception(e.getMessage());
			}			
		}		
	}	

}
